﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.Collections.Specialized;
using System.Reflection;

namespace carApp
{

    public class db
    {        
        #region method
        public static void WriteLog(int typeid,string username,string contents)
        {
            Libs.FileHelper.AppendText("log.txt", Libs.Utils.getDateTime()+"，ID：" + typeid + "，" + username + "，" + contents+"\r\n");
            SqliteHelper.ExecuteSql("insert into logs(typeid,username,contents,times) values("+typeid+",'"+username+ "','" + contents + "','" + Libs.Utils.getDateTime() + "')");
        }
        public static int inc(string table, string where,string cols,int num=1)
        {
            string sql = "update " + table+" set "+cols+"="+cols+" + "+num;
            sql = sql + " where " + where;
            return int.Parse(SqliteHelper.ExecuteScalar(sql, null));
        }
        public static decimal sum(string table,string col,string where="")
        {
            string sql = "select sum("+col+") from " + table;
            if (where.Length > 0)
                sql = sql + " where " + where;
            string _sum = SqliteHelper.ExecuteScalar(sql, null);
            if(_sum == "")
            {
                return 0;
            }
            return decimal.Parse(_sum);
        }
        public static int count(string table,string where="")
        {
            string sql = "select count(*) from " + table;
            if (where.Length > 0)
                sql = sql + " where " + where;
            return int.Parse(SqliteHelper.ExecuteScalar(sql,null));
        }
        public static DataTable GetPagerData(string table, string cols, string where,int pagesize,int currentpage,string orderby="")
        {
            string sql = "select " + cols + " from " + table;
            if (where.Length > 0)
                sql = sql + " where " + where;
            sql=sql + " limit " + pagesize + " offset " + (currentpage - 1) * pagesize;
            if (orderby.Length > 0)
                sql = sql + "order by "+orderby;
            return SqliteHelper.ExecuteTable(sql);
        }
        public static int update(string table,string cols,string where)
        {
            return SqliteHelper.ExecuteSql("update "+table+" set "+cols+" where "+where);
        }
        public static T FillModel<T>(string table,string where="",string cols="") where T : new()
        {
            string sql = "select ";
            if (cols.Length > 0)
                sql = sql + cols;
            else
                sql = sql + "*";
            sql = sql + " from " + table;
            if (where.Length > 0)
                sql = sql + " where " + where;
            DataTable dt= SqliteHelper.ExecuteTable(sql);
            DataRow dr = dt.Rows[0];
            T model = new T();
            for (int i = 0; i < dr.Table.Columns.Count; i++)
            {
                try
                {
                    PropertyInfo propertyInfo = model.GetType().GetProperty(dr.Table.Columns[i].ColumnName);
                    if (propertyInfo != null && dr[i] != DBNull.Value)
                    {
                        object o = dr[i];
                        if (propertyInfo.PropertyType.FullName == "System.Int32")
                        {
                            o = Convert.ToInt32(o);
                        }
                        propertyInfo.SetValue(model, o, null);
                    }
                }
                catch (Exception ex)
                {

                }
            }
            return model;
        }

        public static DataTable GetTable(string table,string where="",string cols="")
        {
            string sql = "select ";
            if (cols.Length > 0)
                sql = sql + cols;
            else
                sql = sql + "*";
            sql = sql + " from " + table;
            if (where.Length > 0)
                sql = sql + " where "+where;
            return SqliteHelper.ExecuteTable(sql);
        }
        public static int add<T>(T obj) where T : class
        {
            StringBuilder cols = new StringBuilder();
            StringBuilder vals = new StringBuilder();
            Type t = obj.GetType();
            string table = t.Name;
            string sql = "insert into " + table + "({0}) values({1})";
            PropertyInfo[] pi = t.GetProperties();
            var keyName = "";
            DataTable dt = SqliteHelper.ExecuteTable("select * from " + table+" limit 0,1");
            foreach (DataColumn col in dt.PrimaryKey)
            {
                keyName = col.ColumnName;
            }
            foreach(PropertyInfo p in pi)
            {
                if (p.Name.ToString() != keyName)
                {
                    cols.Append("'" + p.Name.ToString() + "',");
                    vals.Append("'" + p.GetValue(obj).ToString() + "',");
                }

            }
            sql = string.Format(sql, cols.ToString().Trim(','), vals.ToString().Trim(','));
            return SqliteHelper.ExecuteSql(sql);
        }
        public static int edit<T>(T obj) where T : class
        {           
            Type t = obj.GetType();
            string table = t.Name;
            string sql = "update " + table + " set ";
            PropertyInfo[] pi = t.GetProperties();
            string keyVal = "0";            
            foreach (PropertyInfo p in pi)
            {
                if (p.Name.ToString().ToLower() == "id")
                {
                    keyVal = p.GetValue(obj).ToString();
                }
                else
                {
                    sql = sql + p.Name.ToString()+"='"+ p.GetValue(obj).ToString() + "',";
                }
            }
            sql = sql.Trim(',') + " where id=" + keyVal;
            return SqliteHelper.ExecuteSql(sql);
        }
        public static List<T> FillListModel<T>(DataTable dt)where T : new()
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return null;
            }
            List<T> modelList = new List<T>();
            foreach (DataRow dr in dt.Rows)
            {
                T model = new T();
                for (int i = 0; i < dr.Table.Columns.Count; i++)
                {
                    PropertyInfo propertyInfo = model.GetType().GetProperty(dr.Table.Columns[i].ColumnName);
                    if (propertyInfo != null && dr[i] != DBNull.Value)
                        propertyInfo.SetValue(model, dr[i], null);
                }

                modelList.Add(model);
            }
            return modelList;
        }
        public static T FillModel<T>(DataTable dt)where T : new()
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return default(T);
            }
            DataRow dr = dt.Rows[0];
            T model = new T();
            for (int i = 0; i < dr.Table.Columns.Count; i++)
            {
                try
                {
                    PropertyInfo propertyInfo = model.GetType().GetProperty(dr.Table.Columns[i].ColumnName);
                    if (propertyInfo != null && dr[i] != DBNull.Value)
                    {
                        object o = dr[i];
                        if (propertyInfo.PropertyType.FullName == "System.Int32")
                        {
                            o = Convert.ToInt32(o);
                        }
                        propertyInfo.SetValue(model, o, null);
                    }
                }
                catch(Exception ex)
                {

                }
            }
            return model;
        }
        #endregion
        public static string DataTableToJson(DataTable table)
        {
            if (table.Rows.Count == 0)
                return "[]";
            var JsonString = new StringBuilder();
            JsonString.Append("[");
            for (int i = 0; i < table.Rows.Count; i++)
            {
                JsonString.Append("{");
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (j < table.Columns.Count - 1)
                    {
                        JsonString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\",");
                    }
                    else if (j == table.Columns.Count - 1)
                    {
                        JsonString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\"");
                    }
                }
                if (i == table.Rows.Count - 1)
                {
                    JsonString.Append("}");
                }
                else
                {
                    JsonString.Append("},");
                }
            }
            JsonString.Append("]");
            return JsonString.ToString();
        }
    }
}
